package history

import (
    "github.com/mattn/go-sqlite3"
    "database/sql"
    "fmt"
    "time"
    "recorder/internal"
    "recorder/log"
)

const (
    createJobsTableSql        = `create table if not exists jobs (id string not null primary key, created timestamp, updated timestamp, controller string, worker string, status string not null, error string)`
    createJobsStatusIndexSql  = `create index if not exists jobs_status_index on jobs(status)`
    createJobsUpdatedIndexSql = `create index if not exists jobs_updated_index on jobs(updated)`
    insertJobSql              = `insert into jobs (id, created, updated, controller, worker, status, error) values (?,?,?,?,?,?,?)`
    updateJobSql              = `update jobs set updated=?, controller=?, worker=?, status=?, error=? where id=?`
    deleteOldJobsSql          = `delete from jobs where updated < ?`
)

type Db struct {
    config     *internal.Config
    db         *sql.DB
    closed     chan struct{}
    job_insert chan *JobRecord
    job_update chan *JobRecord
    insert     *sql.Stmt
    update     *sql.Stmt
    clean      *sql.Stmt
}

func NewDb(config *internal.Config) *Db {
    db := &Db{
        config:     config,
        closed:     make(chan struct{}),
        job_insert: make(chan *JobRecord),
        job_update: make(chan *JobRecord),
    }

    sqlite3Db, err := sql.Open("sqlite3", config.HstPath)
    if err != nil {
        log.Error("unable to open sqlite3 db %s: %v", config.HstPath, err)
        panic(err)
    }
    log.Info("opened sqlite3 database %s ", config.HstPath)
    db.db = sqlite3Db

    if _, err := db.db.Exec(createJobsTableSql); err != nil {
        log.Error("%v error: %v", createJobsTableSql, err)
        panic(err)
    }
    if _, err := db.db.Exec(createJobsStatusIndexSql); err != nil {
        log.Error("%v error: %v", createJobsStatusIndexSql, err)
        panic(err)
    }
    if _, err := db.db.Exec(createJobsUpdatedIndexSql); err != nil {
        log.Error("%v error: %v", createJobsStatusIndexSql, err)
        panic(err)
    }

    db.insert, err = db.db.Prepare(insertJobSql)
    if err != nil {
        log.Warning("prepare %v error: %v", insertJobSql, err)
        panic(err)
    }

    db.update, err = db.db.Prepare(updateJobSql)
    if err != nil {
        log.Warning("prepare %v error: %v", updateJobSql, err)
        panic(err)
    }

    db.clean, err = db.db.Prepare(deleteOldJobsSql)
    if err != nil {
        log.Warning("prepare %v error: %v", deleteOldJobsSql, err)
        panic(err)
    }

    return db
}

func (db *Db) Close() {
    db.insert.Close()
    db.update.Close()
    db.clean.Close()

    db.db.Close()
    close(db.closed)
}

func (db *Db) InsertJob(j *JobRecord) {
    select {
    case <-db.closed:
        return
    default:
        db.job_insert <- j
    }
}

func (db *Db) UpdateJob(record *JobRecord) {
    select {
    case <-db.closed:
        return
    default:
        db.job_update <- record
    }
}

func (db *Db) Run() {
    defer db.insert.Close()
    defer db.update.Close()
    defer db.clean.Close()
    defer db.db.Close()

    clean_ticker := time.NewTicker(1 * time.Hour)
    defer clean_ticker.Stop()
    for {
        select {
        case <-db.closed:
            return
        case j := <-db.job_insert:
            _, err := db.insert.Exec(j.Id, j.CreatedAt, j.UpdatedAt, j.Controller, j.Worker, j.Status, j.Error)
            if err != nil {
                if e, ok := err.(sqlite3.Error); ok {
                    if e.ExtendedCode == sqlite3.ErrConstraintPrimaryKey {
                        _, err = db.update.Exec(j.UpdatedAt, j.Controller, j.Worker, j.Status, j.Error, j.Id)
                    }
                }
                if err != nil {
                    log.Warning("history: insert job %s error: %v", j.Id, err)
                }
            }
        case j := <-db.job_update:
            _, err := db.update.Exec(j.UpdatedAt, j.Controller, j.Worker, j.Status, j.Error, j.Id)
            if err != nil {
                log.Warning("history: update job %s error: %v", j.Id, err)
            }
        case now := <-clean_ticker.C:
            _, err := db.clean.Exec(now.Add(-24 * time.Duration(db.config.KeepDays) * time.Hour))
            if err != nil {
                log.Warning("history: clean job error: %v", err)
            }
        }
    }
}

func (db *Db) Read(since, until, status string, limit int) ([]*JobRecord, error) {
    records := make([]*JobRecord, 0)
    where_clause := ``
    order := `asc`
    if len(until) > 0 {
        where_clause += fmt.Sprintf(`where id < '%s'`, until)
        order = `desc`
    } else if len(since) > 0 {
        where_clause += fmt.Sprintf(`where id > '%s'`, since)
        order = `asc`
    } else {
        order = `desc`
    }
    if len(status) > 0 && status != JobStatusUnknown {
        if len(where_clause) > 0 {
            where_clause += ` and `
        } else {
            where_clause += ` where `
        }
        where_clause += fmt.Sprintf(`status = '%s'`, status)
    }
    query := fmt.Sprintf(`select * from (select id, created, updated, controller, worker, status, error from jobs %s order by id %s limit %d) order by id desc`, where_clause, order, limit)
    log.Info("history: read jobs with sql %s", query)
    rows, err := db.db.Query(query)
    if err != nil {
        log.Warning("history: read jobs fail, sql error: %v", err)
        return records, err
    }
    defer rows.Close()
    row_count := 0
    for rows.Next() {
        row_count++
        record := &JobRecord{}
        if err := rows.Scan(&record.Id, &record.CreatedAt, &record.UpdatedAt, &record.Controller, &record.Worker, &record.Status, &record.Error); err != nil {
            log.Warning("history: read jobs fail, sql error: %v", err)
            return records, err
        }
        records = append(records, record)
    }
    log.Info("history: read %d records", row_count)
    return records, nil
}
